package com.fanyouzhao.ehome.util;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

/**
 * DBUtil操作数据库的基本工具类
 * 
 * @author fanyouzhao
 * @date 2021年3月31日 上午10:04:52
 */
public class DBUtil {

	private Connection connection = null;
	private PreparedStatement preparedStatement = null;
	private String driver = null;
	private String url = null;
	private String user = null;
	private String pass = null;
	private ResultSet resultSet = null;

	// 使用类声明的语句块
	// 保证能在获得连接之前先执行
	{
		// 结合IO和静态语句块的功能,加载配置文件
		// 让自定义配置项加载工具Properties实例化,并且和配置文件绑定
		// 实例化Properties对象
		Properties prop = new Properties();
		//服务器不能使用FileInputStream，使用ClassLoader加载
		ClassLoader classLoader = DBUtil.class.getClassLoader();
		InputStream resourceAsStream = classLoader.getResourceAsStream("db.properties");

		try {
			prop.load(resourceAsStream);
			driver = prop.getProperty("JDBC_DRIVER");
			url = prop.getProperty("DB_URL");
			user = prop.getProperty("USER");
			pass = prop.getProperty("PASS");

			Class.forName(driver);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 建立连接的方法
	 * 
	 * @return Connection 连接对象
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private Connection getConnection() throws ClassNotFoundException, SQLException {
		connection = DriverManager.getConnection(url, user, pass);
		return connection;

	}

	/**
	 * 获取预编译语句对象的方法
	 * 
	 * @param sql 预编译的SQL命令
	 * @return PreparedStatement对象
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public PreparedStatement getPreparedStatement(String sql) throws ClassNotFoundException, SQLException {
		preparedStatement = getConnection().prepareStatement(sql);
		return preparedStatement;
	}

	/**
	 * 执行增删改的方法, 执行完毕,回收所有资源
	 * 
	 * @param preparedStatement 执行增删改语句的对象
	 * @return 返回影响的行数
	 * @throws SQLException
	 */
	public int execUpdate(PreparedStatement preparedStatement) throws SQLException {
		return preparedStatement.executeUpdate();
	}

	/**
	 * 执行查询的方法, 执行完毕,回收所有资源
	 * 
	 * @param preparedStatement 执行查询语句的对象
	 * @return 返回相应的结果集
	 * @throws SQLException
	 */
	public ResultSet execQuery(PreparedStatement preparedStatement) throws SQLException {
		resultSet = preparedStatement.executeQuery();
		return resultSet;
	}

	/**
	 * 关闭结果集对象
	 */
	public void closeSet() throws SQLException {
		if (resultSet != null) {
			resultSet.close();
		}
	}

	/**
	 * 关闭预编译对象
	 */
	public void closePrepStat() throws SQLException {
		if (preparedStatement != null) {
			preparedStatement.close();
		}
	}

	/**
	 * 关闭连接对象
	 */
	public void closeConn() throws SQLException {
		if (connection != null) {
			connection.close();
		}
	}

	/**
	 * 关闭全部对象,调用其它三个关闭方法
	 */
	public void closeAll() throws SQLException {
		closeSet();
		closePrepStat();
		closeConn();
	}

}
